package com.ibm.spatiotemp.server.datasources.mockdb;

import java.util.Collection;
import java.util.Iterator;

import org.apache.log4j.Logger;

import com.ibm.spatiotemp.server.datamodel.gpsdata.TSPoint;
import com.ibm.spatiotemp.server.datasources.DataSchema;
import com.ibm.spatiotemp.server.datasources.QueryStringFormatter;
import com.ibm.spatiotemp.tools.RegionBBox;

public class MockdbQueryStringFormatter extends QueryStringFormatter {

	public static Logger logger = Logger.getLogger(MockdbQueryStringFormatter.class);
	
	public static String createMovObjTable_SQL =
			"create table IF NOT EXISTS %s"
			+ "(%s varchar(32) not null," + "%s double not null, "
			+ "%s double not null, %s BIGINT not null, PRIMARY KEY (%s, %s))";/*type timestamp -> BIGINT*/
	
	public String getCreateMovingObjectTableSQL(DataSchema schema){
		String sql = String.format(createMovObjTable_SQL,
				schema.getTab_name(), 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getMovobj_colname(),
				schema.getTs_colname());
		return sql;
	}

	public static String DataScan_SQL = "select %s,%s,%s,%s from %s where %s='%s' and %s >= '%s' and %s <= '%s' order by %s, %s";
	@Override
	public String RawDataScanQueryString(DataSchema schema, String startts,
			String endts, String movobjid) {
		String sql = String.format(DataScan_SQL, 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getTab_name(),
				schema.getMovobj_colname(),
				movobjid,
				schema.getTs_colname(),
				startts,
				schema.getTs_colname(),
				endts,
				schema.getMovobj_colname(),
				schema.getTs_colname()
				);
		return sql;
	}
	
	public static String position_SQL = "select %s,%s,%s,%s from %s where %s = '%s' and (%s >= %f and %s <= %f) and (%s >= %f and %s <= %f) order by %s";
	public String PositionQueryString(DataSchema schema, long time, RegionBBox bbox)
	{
		String sql = String.format(position_SQL, 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getTab_name(),
				schema.getTs_colname(),
				""+time,
				schema.getX_colname(),
				bbox.getX_min(),
				schema.getX_colname(),
				bbox.getX_max(),
				schema.getY_colname(),
				bbox.getY_min(),
				schema.getY_colname(),
				bbox.getY_max(),
				schema.getMovobj_colname()
				);
		return sql;
	}
	
	/*
	 * Method: GET /tenant1?queryType=firsttime&id=uuid1&position=[25,36]&distance=5&start=2014-01-01T00:00:00&end=2014-02-01T00:00:00
	 * */
	public static String firsttime_SQL = "select %s,%s,%s,%s from %s where %s = '%s' and ( %s >= %s and %s <= %s ) and (%s >= %f and %s <= %f) and (%s >= %f and %s <= %f) order by %s";
	public String FirsttimeQueryString(DataSchema schema, String movid, long start, long end, RegionBBox bbox)
	{
		String sql = String.format(firsttime_SQL, 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getTab_name(),
				schema.getMovobj_colname(),
				movid,
				schema.getTs_colname(),
				""+start,
				schema.getTs_colname(),
				""+end,
				schema.getX_colname(),
				bbox.getX_min(),
				schema.getX_colname(),
				bbox.getX_max(),
				schema.getY_colname(),
				bbox.getY_min(),
				schema.getY_colname(),
				bbox.getY_max(),
				schema.getMovobj_colname()
				);
		return sql;
	}
	
	
	public static String Trajectory_Type1_SQL = "select %s,%s,%s,%s from %s where %s='%s' and %s >= '%s' and %s <= '%s' order by %s, %s";
	public String TrajectoryQueryStringType1(DataSchema schema, long startts,
			long endts, String modid) {
		String sql = String.format(Trajectory_Type1_SQL, 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getTab_name(),
				schema.getMovobj_colname(),
				modid,
				schema.getTs_colname(),
				""+startts,
				schema.getTs_colname(),
				""+endts,
				schema.getMovobj_colname(),
				schema.getTs_colname()
				);
		return sql;
	}

	public static String Trajectory_Type2_SQL = "select %s,%s,%s,%s from %s where (%s >= '%s' and %s <= '%s') and (%s >= %f and %s <= %f) and (%s >= %f and %s <= %f) %s order by %s, %s";
	public String TrajectoryQueryStringType2(DataSchema schema, long startts,
			long endts, RegionBBox bbox, String modid/*can be null*/){
		
		String movid_condition = "";
		if(modid != null){
			movid_condition = String.format("and %s='%s' ", schema.getMovobj_colname(), modid);
		}
		
		String sql = String.format(Trajectory_Type2_SQL, 
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname(),
				schema.getTab_name(),
				schema.getTs_colname(),
				""+startts,
				schema.getTs_colname(),
				""+endts,
				schema.getX_colname(),
				bbox.getX_min(),
				schema.getX_colname(),
				bbox.getX_max(),
				schema.getY_colname(),
				bbox.getY_min(),
				schema.getY_colname(),
				bbox.getY_max(),
				movid_condition,
				schema.getMovobj_colname(),
				schema.getTs_colname()
				);
		return sql;
	}

	public static String insertData_SQL = 
			"insert into %s(%s,%s,%s,%s) values ";
	public static String TSPoint_SQL = "('%s',%s,%s,%s)";
	@Override
	public String writeDataString(DataSchema schema,
			Collection<TSPoint> tspoints) {
		String sql = String.format(insertData_SQL,
				schema.getTab_name(),
				schema.getMovobj_colname(),
				schema.getX_colname(),
				schema.getY_colname(),
				schema.getTs_colname());
		StringBuffer buffer = new StringBuffer();
		Iterator<TSPoint> iter = tspoints.iterator();
		if(tspoints.size()>=1)
		{
			TSPoint curpoint = iter.next();
			buffer.append(String.format(TSPoint_SQL,
					curpoint.tsid,curpoint.x,curpoint.y,
					curpoint.time_stamp
							));
			while(iter.hasNext())
			{
				curpoint = iter.next();
				buffer.append(",");
				buffer.append(String.format(TSPoint_SQL,
						curpoint.tsid,curpoint.x,curpoint.y,
						curpoint.time_stamp
						));
			}
			return sql + buffer.toString();
		}
		else{
			logger.error("tspoints set is empty");
			return null;
		}
	}

	
	
}
